<?php

class ReportsController extends Controller
{
	public function action2monthNotice()
	{
	   $this->logAction();
	   $notifications = $this->renewMembersAlert();
	   $this->render('2monthNotice', array( 'notifications'=>$notifications ) );
	}
	
	public function actionExceedGracePeriod()
	{
	   $this->logAction();
	   $notifications = $this->exceedGracePeriod();
	   $this->render('exceedGracePeriod', array( 'notifications'=>$notifications ) );
	}
	
	public function actionLogistics()
	{
	   $this->logAction();
	   $regions = $this->getRegionLogistics();
	   $provinces = $this->getProvinceLogistics();
	   
	   $reports = array();
	   
	   foreach ( $regions as $region ) {
	     $reports[$region['region']] = array();
		 $reports[$region['region']]['region_id'] = $region['region_id'];
		 $reports[$region['region']]['total_members'] = $region['total_members'];
		 $reports[$region['region']]['provinces'] = array();
		 
		 foreach ( $provinces as $province ) {
		    if ( $region['region_id'] == $province['region_id'] ) {
				array_push( $reports[$region['region']]['provinces'], $province );
			}
		 }
	   }
	   
	   
	   $members = array();
	   
	   $excel_link ="#";
	   $excel_logistics = Yii::app()->request->baseUrl.'/?r=Reports/excelLogistics'; 
	   
	   if ( isset($_GET['region_id']) ) {
		   $members = $this->getRegionMembers($_GET['region_id']);
		   $excel_link = Yii::app()->request->baseUrl.'/?r=Reports/excelRegionMembers&region_id='.$_GET['region_id']; ;
	   }
	   
	   if ( isset($_GET['province_id']) ) {
		   $members = $this->getProvinceMembers($_GET['province_id']);
		   $excel_link = Yii::app()->request->baseUrl.'/?r=Reports/excelProvinceMembers&province_id='.$_GET['province_id'];
	   }
	   
	   $this->render('logistics', array( 
	                 'reports'=>$reports, 'members'=>$members, 'excel_link'=> $excel_link,
					 'excel_logistics'=>$excel_logistics ) );
	}
	
	public function getRegionLogistics()
	{
	   $connection = Yii::app()->db;
	   $sql = "SELECT c.*, b.*, count( * ) AS total_members
					FROM gn_members AS a
					LEFT JOIN gn_provinces AS b ON ( a.province_id = b.province_id )
					LEFT JOIN gn_regions AS c ON ( b.region_id = c.region_id )
					GROUP BY c.region_id ";
					
	   $command = $connection->createCommand($sql);
	   $dataReader = $command->query();
	   $regions = array();
	   foreach( $dataReader as $row ) {
	      array_push( $regions, $row ); 
       }
		
	   return $regions;
	}
	
	public function getRegionMembers( $region_id )
	{
	   $connection = Yii::app()->db;
	   $sql = "SELECT a.*, b.*, c.* 
					FROM gn_members AS a
					LEFT JOIN gn_provinces AS b ON ( a.province_id = b.province_id )
					LEFT JOIN gn_regions AS c ON ( b.region_id = c.region_id )
					    WHERE c.region_id = $region_id";
					
	   $command = $connection->createCommand($sql);
	   $dataReader = $command->query();
	   $members = array();
	   foreach( $dataReader as $row ) {
	      array_push( $members, $row ); 
       }
		
	   return $members;
	}
	
	public function getProvinceMembers( $province_id )
	{
	   $connection = Yii::app()->db;
	   $sql = "SELECT a.*, b.*, c.* 
					FROM gn_members AS a
					LEFT JOIN gn_provinces AS b ON ( a.province_id = b.province_id )
					LEFT JOIN gn_regions AS c ON ( b.region_id = c.region_id )
					    WHERE a.province_id = $province_id";
					
	   $command = $connection->createCommand($sql);
	   $dataReader = $command->query();
	   $members = array();
	   foreach( $dataReader as $row ) {
	      array_push( $members, $row ); 
       }
		
	   return $members;
	}
	
	public function getProvinceLogistics()
	{
	   $connection = Yii::app()->db;
	   $sql = "SELECT c.*, b.*, count( * ) AS total_members
					FROM gn_members AS a
					LEFT JOIN gn_provinces AS b ON ( a.province_id = b.province_id )
					LEFT JOIN gn_regions AS c ON ( b.region_id = c.region_id )
					GROUP BY b.province_id ";
					
	   $command = $connection->createCommand($sql);
	   $dataReader = $command->query();
	   $provinces = array();
	   foreach( $dataReader as $row ) {
	      array_push( $provinces, $row ); 
       }
		
	   return $provinces;
	}
	
	public function autoSuspend(){
	    	
		$connection = Yii::app()->db;
		//get all members who needs to renew their membership
		$sql = "SELECT member_id, 
				       date_joined, 
				DATEDIFF( CURRENT_DATE( ) , date_joined ) AS 'numdays'
					FROM gn_members";
		
	   
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		$dataReader->bindColumn(1, $memberid);
		$dataReader->bindColumn(3, $nDays);
		
		while($dataReader->read()!=false){
		    if($nDays>31){
		    	//update member status to suspended
		    	updateStatus("s",$memberid);
		    }
		}
	}
	
	public function updateStatus($status,$id){
		$connection = Yii::app()->db;
		$sql = "UPDATE gn_members SET status=:status WHERE member_id=:id";
		$command = $connection->createCommand($sql);
		$command->bindParam(":status", $status,PDO::PARAM_STR);
		$command->bindParam(":id", $id,PDO::PARAM_STR);
		$command->execute();
		
	}
	
	public function notifyRenewal(){
		$connection=Yii::app()->db;
		$sql ="SELECT member_id,
				email,
				date_joined
				FROM gn_members
				WHERE MONTH(date_joined) = MONTH(DATE_ADD( CURRENT_DATE( ) , INTERVAL 2 MONTH ))
				AND DAYOFMONTH(date_joined) = DAYOFMONTH(DATE_ADD( CURRENT_DATE( ) , INTERVAL 2 MONTH ))";
		
		$command=$connection->createCommand($sql);
		$dataReader = $command->query();
		$dataReader->bindColumn(1, $id);
		$dataReader->bindColumn(2, $email);
		$dataReader->bindColumn(3, $date_joined);
		while($dataReader->read()!==false){
			//send email notification
		}
	}
	
	public function renewMembersAlert() {
		$connection = Yii::app()->db;
		$sql ="SELECT *
				FROM gn_members
				WHERE DATEDIFF( CURRENT_DATE(), DATE_ADD( status_date, INTERVAL 1 YEAR ) ) > 60";
		
		$command = $connection->createCommand($sql);
	    $dataReader = $command->query();
	    $notifications = array();
		
	    foreach( $dataReader as $row ) {
	      array_push( $notifications, $row ); 
        }
		
	    return $notifications;
	}
	
	public function exceedGracePeriod() {
		$connection = Yii::app()->db;
		$sql ="SELECT *
				FROM gn_members
				WHERE DATEDIFF( DATE_ADD(status_date,INTERVAL 1 YEAR), CURRENT_DATE() ) < -31";
		
		$command = $connection->createCommand($sql);
	    $dataReader = $command->query();
	    $notifications = array();
		
	    foreach( $dataReader as $row ) {
	      array_push( $notifications, $row ); 
        }
		
	    return $notifications;
	}

	// Uncomment the following methods and override them if needed
	/*
	public function filters()
	{
		// return the filter configuration for this controller, e.g.:
		return array(
			'inlineFilterName',
			array(
				'class'=>'path.to.FilterClass',
				'propertyName'=>'propertyValue',
			),
		);
	}

	public function actions()
	{
		// return external action classes, e.g.:
		return array(
			'action1'=>'path.to.ActionClass',
			'action2'=>array(
				'class'=>'path.to.AnotherActionClass',
				'propertyName'=>'propertyValue',
			),
		);
	}
	*/
	
	public function behaviors(){
	
	    return array(
            'eexcelview'=>array(
                'class'=>'ext.eexcelview.EExcelBehavior',
            ),
        );
	}
	
	public function actionExcelRegionMembers() {  
	   $this->logAction();
	   $members = $this->getRegionMembers( $_GET['region_id'] );
	   $dataProvider = new CArrayDataProvider($members);
	   $this->toExcel($dataProvider); 
	}
	
	
	public function actionExcelProvinceMembers() {  
	   $this->logAction();
	   $members = $this->getProvinceMembers( $_GET['province_id'] );
	   $dataProvider = new CArrayDataProvider($members);
	   $this->toExcel($dataProvider); 
	}
	
	public function actionExcelLogistics() {  
	   $this->logAction();
	   $regions = $this->getRegionLogistics();
	   $provinces = $this->getProvinceLogistics();
	   
	   $reports = array();
	   
	   foreach ( $regions as $region ) {
		 $data['region'] = $region['region'];
		 $data['Members'] = $region['total_members'];
		 $data['province'] = "";
		 $data['pvMembers'] = "";
		 array_push( $reports, $data );
		 
		 //print_r ( $region );  
	     //array_push( $reports, array( 'region'=>$region['region'], 'total_members'=>$region['total_members'] ) );
		 
		 foreach ( $provinces as $province ) {
		    if ( $region['region_id'] == $province['region_id'] ) {
				$data['region'] = "";
		        $data['Members'] = "";
				$data['province'] = $province['province'];
				$data['pvMembers'] = $province['total_members'];
				array_push( $reports, $data );
				//array_push( $reports, array( 'province'=>$province['province'], 'total_members'=>$province['total_members'] ) );
			}
		 }
	   }
	   
	   $dataProvider = new CArrayDataProvider($reports);
	   $this->toExcel($dataProvider); 
	}
	
	public function actionPrint2Excel(){
	    //Load data
		$model= new Member();
		$data= $model->findAll();
		//Export it
		$this->toExcel($data);
	   
	}
	
	public function actionPrint2PDF(){
	
	    $model=Members::model()->findAll();
		//Export it
		$this->toExcel($model,'','Members Report',array('creator'=>'zen','autoWidth'=>true),'PDF');
	}
	
	public function array_implode( $glue, $separator, $array ) {
		if ( ! is_array( $array ) ) return $array;
		$string = array();
		foreach ( $array as $key => $val ) {
			if ( is_array( $val ) )
				$val = implode( ',', $val );
			$string[] = "{$key}{$glue}{$val}";
		   
		}
		
		return implode( $separator, $string );
	}
	
	public function logAction()
	{
		$datetime = date("Y-m-d H:i:s");
		$user = Yii::app()->user->id;
		$action =  $this->id . '/' . $this->action->id;
		$data   = $this->array_implode( "=", "::", $_GET );
		$data  .= $this->array_implode( "=", "::", $_POST );
		
		$sql = "INSERT INTO gn_audit_trail SET
		               datetime='$datetime', 
					   user='$user',
					   action='$action',
					   data='$data'";
					   
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
	}
}